# front.py
import streamlit as st
import pandas as pd
from backtrader.backtrader import AdvancedBackTrader
from sql.SQLServiceMgr import SQLServiceMgr
import plotly.io as pio
import numpy as np

# 设置全局绘图模板
pio.templates.default = "plotly_white"

def main():
    st.title("📈 量化回测分析平台")
    mgr = SQLServiceMgr()
    
    # 使用侧边栏作为导航
    st.sidebar.header("导航")
    page = st.sidebar.radio("选择页面", ["回测结果分析", "数据管理"])
    
    if page == "回测结果分析":
        render_backtest_analysis(mgr)
    elif page == "数据管理":
        render_data_management(mgr)

def render_backtest_analysis(mgr):
    """回测结果分析页面"""
    st.header("📊 回测结果分析")
    
    # 获取回测列表
    backtests = mgr.get_backtest_list()
    
    if not backtests:
        st.warning("没有找到任何回测结果")
        return
    
    # 创建回测选择器
    selected_bt = st.selectbox(
        "选择回测结果", 
        backtests,
        format_func=lambda bt: f"{bt['name']} ({bt['start_time'].date()} 至 {bt['end_time'].date()})"
    )
    
    backtest_name = selected_bt['name']
    
    if st.button("加载回测详情", use_container_width=True):
        with st.spinner("加载数据中..."):
            try:
                # 获取回测详情
                summary, daily_df, trade_details_df = mgr.get_backtest_detail(backtest_name)
                
                if not summary:
                    st.error("无法加载回测详情")
                    return
                
                # 显示关键指标卡片
                st.subheader("📈 绩效概览")
                col1, col2, col3 = st.columns(3)
                col1.metric("初始资金", f"¥{summary['initial_cash']:,.2f}")
                col2.metric("最终资产", f"¥{summary['final_value']:,.2f}", 
                          f"{summary['total_return']:+.2f}%")
                col3.metric("年化收益", f"{summary['annual_return']:.2f}%")
                
                col1, col2, col3 = st.columns(3)
                col1.metric("最大回撤", f"{summary['max_drawdown']:.2f}%")
                col2.metric("夏普比率", f"{summary['sharpe_ratio']:.2f}")
                col3.metric("胜率", f"{summary['win_rate']:.2f}%")
                
                # 显示详细指标表
                st.subheader("📋 详细绩效指标")
                metrics_data = [
                    ("总交易次数", summary['total_trades']),
                    ("交易天数", summary['trade_days']),
                    ("盈利交易", summary['win_trades']),
                    ("亏损交易", summary['loss_trades']),
                    ("盈亏比", f"{summary['profit_factor']:.2f}"),
                    ("平均盈利", f"¥{summary['avg_win']:,.2f}"),
                    ("平均亏损", f"¥{summary['avg_loss']:,.2f}"),
                    ("波动率", f"{summary['volatility']:.2f}%"),
                    ("索提诺比率", f"{summary['sortino_ratio']:.2f}"),
                    ("期末持仓比例", f"{summary['end_position_ratio']:.2f}%")
                ]
                
                # 分两列展示指标
                col1, col2 = st.columns(2)
                for i, (name, value) in enumerate(metrics_data):
                    if i % 2 == 0:
                        col1.metric(name, value)
                    else:
                        col2.metric(name, value)
                
                # 修复时间轴问题 - 确保日期格式正确
                daily_df.index = pd.to_datetime(daily_df.index)
                if not daily_df.empty:
                    # 设置图表的时间范围从回测开始前一天到结束后一天
                    start_date = daily_df.index.min() - pd.Timedelta(days=1)
                    end_date = daily_df.index.max() + pd.Timedelta(days=1)
                else:
                    start_date = summary['start_time'] - pd.Timedelta(days=1)
                    end_date = summary['end_time'] + pd.Timedelta(days=1)
                
                # 显示图表
                st.subheader("📊 绩效图表")
                bt = AdvancedBackTrader(pd.DataFrame(), cash=summary['initial_cash'])
                fig = bt._plot_performance(daily_df, trade_details_df)
                
                # 更新图表的时间范围
                if 'xaxis' in fig.layout:
                    fig.update_layout(
                        xaxis=dict(range=[start_date, end_date]),
                        xaxis2=dict(range=[start_date, end_date]),
                        xaxis5=dict(range=[start_date, end_date]),
                        xaxis6=dict(range=[start_date, end_date])
                    )
                
                st.plotly_chart(fig, use_container_width=True)
                
                # 显示交易详情
                if not trade_details_df.empty:
                    st.subheader("💹 交易详情")
                    st.dataframe(
                        trade_details_df,
                        column_config={
                            "date": "日期",
                            "symbol": "标的",
                            "action": st.column_config.SelectboxColumn(
                                "操作",
                                options=["buy", "sell"],
                                required=True
                            ),
                            "price": st.column_config.NumberColumn(
                                "价格",
                                format="¥%.4f"
                            ),
                            "amount": "数量",
                            "fee": st.column_config.NumberColumn(
                                "手续费",
                                format="¥%.2f"
                            ),
                            "profit": st.column_config.NumberColumn(
                                "盈亏",
                                format="¥%.2f"
                            ),
                            "return_pct": st.column_config.ProgressColumn(
                                "收益率(%)",
                                format="%.2f%%",
                                min_value=-100,
                                max_value=100
                            )
                        },
                        hide_index=True,
                        use_container_width=True
                    )
                
            except Exception as e:
                st.error(f"加载回测详情失败: {str(e)}")

def render_data_management(mgr):
    """数据管理页面"""
    st.header("🗄️ 数据管理")
    
    # 新增：标的代码查询功能
    st.subheader("🔍 标的代码查询")
    symbols_input = st.text_input("输入标的代码（多个用逗号分隔）", placeholder="例如: SHSE.600000, SZSE.000001")
    
    if st.button("查询标的代码", use_container_width=True):
        if not symbols_input:
            st.warning("请输入要查询的标的代码")
            return
            
        symbols = [s.strip() for s in symbols_input.split(",") if s.strip()]
        with st.spinner("查询中..."):
            try:
                # 获取分钟数据和tick数据的所有标的及时间范围
                min_ranges = mgr.show_exist_symbols("min")
                tick_ranges = mgr.show_exist_symbols("tick")
                
                data = []
                for symbol in symbols:
                    # 检查分钟数据 - 修正列名访问方式
                    min_exists = min_ranges.loc[min_ranges['symbol'] == symbol]
                    # 检查tick数据 - 修正列名访问方式
                    tick_exists = tick_ranges.loc[tick_ranges['symbol'] == symbol]
                    # 获取时间范围字符串
                    min_range_str = "无数据"
                    if not min_exists.empty:
                        # 提取所有时间段并格式化为字符串
                        min_ranges_list = [
                            f"{start.strftime('%Y-%m-%d')} 至 {end.strftime('%Y-%m-%d')}"
                            for start, end in min_exists['time_range']
                        ]
                        min_range_str = "; ".join(min_ranges_list)
                    tick_range_str = "无数据"
                    if not tick_exists.empty:
                        tick_ranges_list = [
                            f"{start.strftime('%Y-%m-%d')} 至 {end.strftime('%Y-%m-%d')}"
                            for start, end in tick_exists['time_range']
                        ]
                        tick_range_str = "; ".join(tick_ranges_list)
                    
                    data.append({
                        "symbol": symbol,
                        "分钟数据": "✅" if not min_exists.empty else "❌",
                        "分钟数据时间范围": min_range_str,
                        "tick数据": "✅" if not tick_exists.empty else "❌",
                        "tick数据时间范围": tick_range_str
                    })
                
                df = pd.DataFrame(data)
                print(df)
                st.dataframe(
                    df,
                    column_config={
                        "symbol": "标的代码",
                        "分钟数据": st.column_config.TextColumn("分钟数据"),
                        "分钟数据时间范围": st.column_config.TextColumn("分钟数据时间范围"),
                        "tick数据": st.column_config.TextColumn("tick数据"),
                        "tick数据时间范围": st.column_config.TextColumn("tick数据时间范围")
                    },
                    hide_index=True,
                    use_container_width=True
                )
                
            except Exception as e:
                st.error(f"查询失败: {str(e)}")

if __name__ == "__main__":
    main()